﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MODEL;
using MySql.Data.MySqlClient;
using Dapper;
using Common;

namespace DAL
{
    public class StationDAL
    {
        public static async Task<IEnumerable<StationInfo>> QueryStationsAsync(StationQueryCriteria criteria = null)
        {
            StationQueryCriteria queryCriteria = criteria ?? new StationQueryCriteria();
            string sql = @"SELECT S.stationName 
                                , S.stationId
                                , S.stationCode
                                , S.stationType 
                                , S.stationAddress
                                , S.countyName
                                , S.cityName
                                , S.provinceName
                                , S.lon
                                , S.lat
                                , S.devinceName AS deviceName
                                , S.hardwareId
                                , S.hardwareCode
                                , S.valve_noList as Valve_NoListStr
                                , S.ctrlFunction
                                , S.version
                                , S.battery
                                , S.ip
                                , S.sim
                                , S.heartBeatInterval
                                , S.note
                                , S.enabled
                                , S.elementList                            
                                FROM msap_db.station_infor S
                                LEFT JOIN msap_db.station_user U
                                ON U.stationId = S.stationId
                                WHERE 
                                (@UserId IS NULL OR U.userId = @UserId)
                                AND (@StationType IS NULL OR S.stationType = @StationType)
                                AND (@ProvinceName IS NULL OR S.provinceName = @ProvinceName)
                                AND (@CityName IS NULL OR S.cityName = @CityName)
                                AND (@CountyName IS NULL OR S.countyName = @CountyName)
                                ORDER BY S.stationId DESC";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.QueryAsync<StationInfo>(sql,new {
                    UserId = queryCriteria.UserId,
                    StationType =  queryCriteria.StationType,
                    ProvinceName = queryCriteria.ProvinceName,
                    CityName = queryCriteria.CityName,
                    CountyName = queryCriteria.CountyName
                    });
            }
        }
       
        public static async Task<StationData> GetStationCountDataByIdAsync(string countDataId)
        {
            string sql = @"SELECT countDataId AS StationDataId
                        , stationid
                        , sampleTime
                        , insertTime
                        , elementCode
                        , elementVal 
                        from msap_db.station_countdata 
                        WHERE countDataId = @CountDataId
                        Limit 1";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.QueryFirstAsync<StationData>(sql, new { CountDataId = countDataId });
            }
        }

        public static async Task<StationData> GetStationRealDataByIdAsync(string realDataId)
        {
            string sql = @"SELECT realDataId AS StationDataId
                            ,stationid
                            ,sampleTime
                            ,insertTime
                            ,elementCode
                            ,elementVal 
                            FROM msap_db.station_realdata 
                            WHERE realDataId = @RealDataId
                            Limit 1";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.QueryFirstAsync<StationData>(sql, new { RealDataId = realDataId });
            }
        }

        public static async Task<int> DeleteStations(List<int> stationIdList)
        {
            string stationIdStr = string.Join(",", stationIdList);

            string sql = $@"DELETE FROM 
                            msap_db.station_infor
                            WHERE stationId IN({stationIdStr});

                            DELETE FROM
                            msap_db.station_user
                            WHERE stationId IN({stationIdStr});";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.ExecuteAsync(sql);
            }
        }

        public static async Task<int> UpdateStationElementListAsync(int stationId,List<string> elementCodes)
        {
            string elementCodeStr = string.Empty;
            if (elementCodes.SafeCheck())
            {
                elementCodeStr = string.Join(",", elementCodes);
            }
            string sql = @"UPDATE msap_db.station_infor
                            SET elementList = @ElementListStr
                            WHERE stationId = @StationId LIMIT 1";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.ExecuteAsync(sql, new
                {
                    ElementListStr = elementCodeStr,
                    StationId = stationId
                });
            }
        }

        public static async Task<int> InsertOrUpdateStationAsync(StationInfo stationInfo)
        {
            string sql = @"	INSERT INTO  msap_db.station_infor
	                        (
                            stationId	 
	                        , stationName
	                        , stationCode
	                        , stationType 
	                        , stationAddress
	                        , countyName
	                        , cityName
	                        , provinceName
	                        , lon
	                        , lat
	                        , devinceName
	                        , hardwareId
	                        , hardwareCode
	                        , valve_noList
	                        , ctrlFunction
	                        , version
	                        , battery
	                        , ip
	                        , sim
	                        , heartBeatInterval
	                        , note
	                        , enabled
	                        , elementList 
	                        )
	                        VALUES(@StationId , @StationName, @StationCode, @StationType , @StationAddress, @CountyName, @CityName, @ProvinceName, @Lon, @Lat, @DevinceName, @HardwareId, @HardwareCode, @Valve_noList, @CtrlFunction, @Version, @Battery, @Ip, @Sim, @HeartBeatInterval, @Note, @Enabled, @ElementList )
	                        ON DUPLICATE KEY UPDATE                            
	                        stationName=VALUES(stationName)
	                        ,stationCode=VALUES(stationCode)
	                        ,stationType =VALUES(stationType )
	                        ,stationAddress=VALUES(stationAddress)
	                        ,countyName=VALUES(countyName)
	                        ,cityName=VALUES(cityName)
	                        ,provinceName=VALUES(provinceName)
	                        ,lon=VALUES(lon)
	                        ,lat=VALUES(lat)
	                        ,devinceName=VALUES(devinceName)
	                        ,hardwareId=VALUES(hardwareId)
	                        ,hardwareCode=VALUES(hardwareCode)
	                        ,valve_noList=VALUES(valve_noList)
	                        ,ctrlFunction=VALUES(ctrlFunction)
	                        ,version=VALUES(version)
	                        ,battery=VALUES(battery)
	                        ,ip=VALUES(ip)
	                        ,sim=VALUES(sim)
	                        ,heartBeatInterval=VALUES(heartBeatInterval)
	                        ,note=VALUES(note)
	                        ,enabled=VALUES(enabled)
	                        ,elementList =VALUES(elementList)";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.ExecuteAsync(sql, new
                {
                    StationId = stationInfo.StationId,
                    StationName = stationInfo.StationName,
                    StationCode = stationInfo.StationCode,
                    StationType = stationInfo.StationType,
                    StationAddress = stationInfo.StationAddress,
                    CountyName = stationInfo.CountyName,
                    CityName = stationInfo.CityName,
                    ProvinceName = stationInfo.ProvinceName,
                    Lon = stationInfo.Lon,
                    Lat = stationInfo.Lat,
                    DevinceName = stationInfo.DeviceName,
                    HardwareId = stationInfo.HardWareId,
                    HardwareCode = stationInfo.HardWareCode,
                    Valve_noList = stationInfo.Valve_NoListStr,
                    CtrlFunction = stationInfo.CtrlFunction,
                    Version = stationInfo.Version,
                    Battery = stationInfo.Battery,
                    Ip = stationInfo.Ip,
                    Sim = stationInfo.Sim,
                    HeartBeatInterval = stationInfo.HeartBeatInterval,
                    Note = stationInfo.Note,
                    Enabled = stationInfo.Enabled,
                    ElementList = stationInfo.ElementList
                });
            }
        }

        public static async Task<int> MapUserWithStationAsync(string userId, List<StationInfo> stationInfos)
        {
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                conn.Open();
                MySqlTransaction tran = await conn.BeginTransactionAsync();
                try
                {
                    string sql = "DELETE FROM msap_db.station_user WHERE userId = @UserId";
                    await conn.ExecuteAsync(sql, new { UserId = userId });
                    sql = $@"INSERT INTO msap_db.station_user
                            (
                                stationId
                                , stationCode
                                , stationType
                                , userId
                            )
                            values 
                            {string.Join(",", stationInfos.Select(
                                 station => $@"
                                (
                                    {station.StationId},
                                    '{station.StationCode}',
                                    '{station.StationType}',
                                    '{userId}'                           
                                )"))}";
                    int count = await conn.ExecuteAsync(sql);
                    tran.Commit();
                    return count;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
                finally
                {
                    tran?.Dispose();
                }
            }
        }
    }
}
